namespace ActiveRecord

import System
import System.Collections.Generic
import System.Data
import System.Reflection
import System.Text
import Inflector.Net

partial class ActiveRecordBase:
   
   // protected / private statics
   private static def getAssociatedWhereTableName(assn as AssociationAttribute, rec as ActiveRecordBase, table as string):
      # EX: order customer: from customers where id = 1, post blog: from blog_table where id = self[parent_id]
      debug "getAssociatedWhereTableName(${rec.TableName}, ${assn.AssociationType}, ${table})"
      #debug "  rec: ${rec}"
      joinTableName as string
      
      if assn.AssociationType == AssociationType.HasOne or assn.AssociationType == AssociationType.HasMany:
         if not Utils.IsEmpty(assn.Through):
            joinTableName = assn.Through
         
         pkVal = rec[rec.PrimaryKey]
         
         if Utils.IsEmpty(pkVal):
            #yield null
            #yield null
            #return
            pkVal = "@@identity"
         
         fkName = rec.getFKName(table, assn)
         debug "getAssociatedWhereTableName() fk: ${fkName}, pkVal: ${pkVal}"
         
         typeName = getAssociatedTypeNameFromTable(table, assn)
         joinPK = _activeRecordTypes[typeName].PrimaryKey
         
         # if association foreign key defined use it, else if through defined use it, else pluralize
         if not Utils.IsEmpty(joinTableName):
            if rec.HasAndBelongsToMany.ContainsKey(joinTableName):
               if not Utils.IsEmpty(rec.HasAndBelongsToMany[joinTableName].AssociationForeignKey):
                  joinFK = rec.HasAndBelongsToMany[joinTableName].AssociationForeignKey
               elif not Utils.IsEmpty(rec.HasAndBelongsToMany[joinTableName].Table):
                  joinFK = "${Inflector.Singularize(rec.HasAndBelongsToMany[joinTableName].Table)}_${DEFAULT_IDENTITY_KEY}"
               else:
                  joinFK = "${Inflector.Singularize(table)}_${DEFAULT_IDENTITY_KEY}"
            else:
               joinFK = "${Inflector.Singularize(table)}_${DEFAULT_IDENTITY_KEY}"
            
            tableName = "${joinTableName} join ${table} on ${table}.${joinPK} = ${joinTableName}.${joinFK}"
         else:
            tableName = table
         
         where = SQL.Where(fkName, pkVal)
         
      # EX: customer orders: from orders where customer_id = 1, blog posts: from post_table where parent_id = self[id]
      elif assn.AssociationType == AssociationType.BelongsTo:
         tableName = table
         fkName = rec.getFKName(table, assn)
         fkVal = rec[fkName]
         if Utils.IsEmpty(fkName) or Utils.IsEmpty(fkVal):
            #raise "${TableName} associated ${table} no ForeignKey found.  ForeignKey: ${fkName}, Value: ${fkVal}"
            yield null
            yield null
            return # don't load the association if can't find foreign key value 
         #debug "loadAssociation() AssociationType.BelongsTo: fk: ${fkName}, fkVal: ${fkVal}"
         #debug "_activeRecordTypes.ContainsKey(${table}): ${_activeRecordTypes.ContainsKey(table)}"
         # check to see if an active record has been created with this table name
         typeName = getAssociatedTypeNameFromTable(table, assn)
         if _activeRecordTypes.ContainsKey(typeName):
            ar = _activeRecordTypes[typeName] as ActiveRecordBase
            where = SQL.Where(ar.PrimaryKey, fkVal)
         else:
            # no active records defined with this table name, try using 'id' as primary key name
            where = SQL.Where(DEFAULT_IDENTITY_KEY, fkVal)
      elif assn.AssociationType == AssociationType.HasAndBelongsToMany:
         if not Utils.IsEmpty(assn.Through):
            joinTableName = assn.Through
         else:
            if rec.TableName.CompareTo(table) == -1:
               joinTableName = "${rec.TableName}_${table}"  
            else:
               joinTableName = "${table}_${rec.TableName}"
         
         #debug "${rec.TableName}.loadAssociation(${table}) - HasAndBelongsToMany - joinTableName: ${joinTableName}"
         fkName = rec.getFKName(table, assn)
         fkVal = rec[rec.PrimaryKey]
         typeName = getAssociatedTypeNameFromTable(table, assn)
         joinPK = _activeRecordTypes[typeName].PrimaryKey
         
         # if association foreign key defined use it, else pluralize
         if rec.HasAndBelongsToMany.ContainsKey(joinTableName) and not Utils.IsEmpty(rec.HasAndBelongsToMany[joinTableName].AssociationForeignKey):
            joinFK = rec.HasAndBelongsToMany[joinTableName].AssociationForeignKey
         else:
            joinFK = "${Inflector.Singularize(table)}_${DEFAULT_IDENTITY_KEY}"
            
         tableName = "${joinTableName} join ${table} on ${table}.${joinPK} = ${joinTableName}.${joinFK}"
         
         # select * from developers_projects join projects on projects.id = developers_projects.project_id where developer_id = 32;
         where = SQL.Where("${joinTableName}.${fkName}", fkVal)
      
      yield where
      yield tableName
      
   private static def getAssociatedTypeNameFromTable(table as string, assn as AssociationAttribute) as string:
      #debug "getAssociatedTypeNameFromTable(${table}, ${assn})"
      typeName as string
      # if the association specifies a className, use it
      if not assn is null and not Utils.IsEmpty(assn.ClassName):
         #debug "searching for ${assn.ClassName} in ${join(_activeRecordTypeNamesShort.Keys, ',')}"
         typeName = _activeRecordTypeNamesShort[assn.ClassName] if _activeRecordTypeNamesShort.ContainsKey(assn.ClassName)
      else:
         #debug "searching for ${table} in ${join(_activeRecordTables, ',')}"
         idx = _activeRecordTables.IndexOf(table)
         if idx >= 0:
            typeName = _activeRecordClasses[idx]
      return typeName if not Utils.IsEmpty(typeName)
      return null

   private static def getInheritanceBaseType(derivedType as Type) as Type:
      #debug "getInheritanceBaseType(${derivedType})" 
      if derivedType == null or not derivedType.IsSubclassOf(typeof(ActiveRecordBase)):
         return derivedType
      else:
         if derivedType.BaseType == typeof(ActiveRecordBase):
            return derivedType
         #ar = getTypeInstance(derivedType)
         #arBase = getTypeInstance(derivedType.BaseType)
         #if ar.usesInheritance() and (arBase == null or not arBase.usesInheritance()):
         if usesInheritance(derivedType) and not usesInheritance(derivedType.BaseType):
            return derivedType
         else: 
            return getInheritanceBaseType(derivedType.BaseType)

   private static def getInsertCmd(activeRecord as ActiveRecordBase) as string:
      return getInsertCmd(activeRecord.GetType().AssemblyQualifiedName, activeRecord._columns)
   
   private static def getInsertCmd(typeName as string, columns as Hash) as string:
      #debug "getInsertCmd(${typeName}) - columns: ${join(columns.Keys, ',')}"
      table = getTypeInstance(typeName).TableName
      sbVals = StringBuilder()
      sbCols = StringBuilder()
      for item in columns:
         if not Utils.IsEmpty(item.Value) and isPossibleColumn(typeName, item.Key):
            sbVals.Append("'" + item.Value.ToString().Replace("'", "''") + "'" + ", ")
            sbCols.Append(item.Key.ToString() + ", ")

      if sbVals.Length > 0:
         sbVals.Length = sbVals.Length - 2
         sbCols.Length = sbCols.Length - 2
      
      return "insert into ${table} (${sbCols}) values (${sbVals});"

   private static def getSelectCmdFromConditions(activeRecord as ActiveRecordBase, *conditions as (Expression)) as string:
      return getSelectCmdFromConditions(activeRecord.TableName, *conditions)
   
   private static def getSelectCmdFromConditions(tableName as string, *conditions as (Expression)) as string:
      // build the sql command
      sbSql = StringBuilder("select * from ${tableName}")

      wheres = List of WhereExpression()
      orderBys = List of OrderByExpression()
      groupBys = List of GroupByExpression()
      havings = List of HavingExpression()

      #debug "conditions.Length: ${conditions.Length}"
      
      for exp as Expression in conditions:
         #debug "exp.Type: " + exp.GetType() if not exp is null
         if exp isa WhereExpression:
            wheres.Add(exp)
         elif exp isa OrderByExpression:
            orderBys.Add(exp)
         elif exp isa GroupByExpression:
            groupBys.Add(exp)
         elif exp isa HavingExpression:
            havings.Add(exp)

      #debug "wheres.Count: ${wheres.Count}, orderBys.Count: ${orderBys.Count}, groupBys.Count: ${groupBys.Count}, havings.Count: ${havings.Count}"

      if wheres.Count > 0:
         sbSql.Append(SQL.BuildWhereStmt(*array(wheres)))
      if orderBys.Count > 0:
         sbSql.Append(SQL.BuildOrderByStmt(*array(orderBys)))
      if groupBys.Count > 0:
         sbSql.Append(SQL.BuildGroupByStmt(*array(groupBys)))
      if havings.Count > 0:
         sbSql.Append(SQL.BuildHavingStmt(*array(havings)))

      return sbSql.ToString()

   // if _activeRecordTypes has record, returns that record or base class instance if uses inheritance
   // otherwise, createds a new instance of the type
   private static def getTypeInstance(type as Type) as ActiveRecordBase:
      #debug "getTypeInstance(${type.AssemblyQualifiedName})"# activeRecordTypes: ${join(_activeRecordTypes.Keys, ',')}"
      if _activeRecordTypes.ContainsKey(type.AssemblyQualifiedName):
         rec = _activeRecordTypes[type.AssemblyQualifiedName]
         if rec.usesInheritance() and _activeRecordBaseTypes.ContainsKey(type.AssemblyQualifiedName) and _activeRecordTypes.ContainsKey(_activeRecordBaseTypes[type.AssemblyQualifiedName]):
            return _activeRecordTypes[ _activeRecordBaseTypes[type.AssemblyQualifiedName] ]
         else:
            return rec
      else:
         return Activator.CreateInstance(type) as ActiveRecordBase
   
   private static def getTypeInstance(typeName as string) as ActiveRecordBase:
      if Utils.IsEmpty(typeName): 
         return null
      return getTypeInstance(Type.GetType(typeName))
   
   private static def getUpdateCmd(tableName as string, setStmt as string, *wheres as (WhereExpression)) as string:
      whereStmt = SQL.BuildWhereStmt(*wheres)
      return "update ${tableName} set ${setStmt} ${whereStmt}"
   
   private static def getUpdateCmd(activeRecord as ActiveRecordBase) as string:
      setStmt = activeRecord.ToString(activeRecord.PrimaryKey, UPDATED_AT, UPDATED_ON, CREATED_ON, CREATED_AT)

      if activeRecord.HasColumn(UPDATED_AT):
         setStmt += ", updated_at = CURRENT_TIMESTAMP"
      elif activeRecord.HasColumn(UPDATED_ON):
         setStmt += ", updated_on = CURRENT_TIMESTAMP"

      return "update ${activeRecord.TableName} set ${setStmt} where ${activeRecord.PrimaryKey} = ${activeRecord[activeRecord.PrimaryKey]}"
   
   
   private static def getDefaultJoinTableName(activeRecord as ActiveRecordBase, otherTable as string) as string:
      if activeRecord.TableName.CompareTo(otherTable) == -1:
         return "${activeRecord.TableName}_${otherTable}"  
      else:
         return "${otherTable}_${activeRecord.TableName}"
   
   private static def getJoinSqlWithThrough(table as string, assn as AssociationAttribute, fkName as string, activeRecord as ActiveRecordBase, typeName as string) as string:
      if not Utils.IsEmpty(assn.Through):
         joinTableName = assn.Through
      else:
         joinTableName = getDefaultJoinTableName(activeRecord, table)
      
      #debug "${rec.TableName}.loadAssociation(${table}) - HasAndBelongsToMany - joinTableName: ${joinTableName}"
      #fkVal = activeRecord[activeRecord.PrimaryKey]
      joinTypeInstance = _activeRecordTypes[typeName]
      joinPK = joinTypeInstance.PrimaryKey
      
      # if association foreign key defined use it, else pluralize
      if activeRecord.HasAndBelongsToMany.ContainsKey(joinTableName) and not Utils.IsEmpty(activeRecord.HasAndBelongsToMany[joinTableName].AssociationForeignKey):
         joinAssn = activeRecord.HasAndBelongsToMany[joinTableName]
         #joinTableFKName = joinTypeInstance.getFKName(joinTableName, joinAssn)
         joinFK = joinAssn.AssociationForeignKey
      else:
         joinFK = "${Inflector.Singularize(table)}_${DEFAULT_IDENTITY_KEY}"
      
      
      #return "${joinTableName} join ${table} on ${table}.${joinPK} = ${joinTableName}.${joinFK}"
      #return "join ${joinTableName} on ${joinTableName}.${joinFK} = ${activeRecord.TableName}.${fkName} join ${table} on ${table}.${joinPK} = ${joinTableName}.${joinFK}"
      return "left outer join ${joinTableName} on ${joinTableName}.${fkName} = ${activeRecord.TableName}.${activeRecord.PrimaryKey} left outer join ${table} on ${table}.${joinPK} = ${joinTableName}.${joinFK}"
      
   private static def getJoinSql(table as string, assn as AssociationAttribute, fkName as string, activeRecord as ActiveRecordBase, columns as List of string) as string:
      debug "getJoinSql(${table}, ${assn.Table}/${assn.ClassName}, ${fkName}, ${activeRecord.GetType()})" 
      typeName = getAssociatedTypeNameFromTable(table, assn)
      ar = getTypeInstance(typeName)
      table = ar.TableName
      joinPK = ar.PrimaryKey
      if columns != null:
         columns.Add(string.Join(", ",  array("${table}.${col} as ${table}_${col}" for col in _activeRecordTypeColumns[typeName])))
      
      if assn.AssociationType == AssociationType.BelongsTo:
         return "left outer join ${table} on ${table}.${joinPK} = ${activeRecord.TableName}.${fkName} "
      elif assn.AssociationType == AssociationType.HasOne or assn.AssociationType == AssociationType.HasMany:
         if not Utils.IsEmpty(assn.Through):
            return getJoinSqlWithThrough(table, assn, fkName, activeRecord, typeName)
         else:
            return "left outer join ${table} on ${table}.${fkName} = ${activeRecord.TableName}.${activeRecord.PrimaryKey} "
      elif assn.AssociationType == AssociationType.HasAndBelongsToMany:
         #raise NotImplementedException ("need to create join for HasAndBelongsToMany")
         return getJoinSqlWithThrough(table, assn, fkName, activeRecord, typeName)
         // developers join developers_projects on developers_projects.project_id = developers.developer_id join projects on projects.id = developers_projects.project_id 
      return null
   
   private static def isPossibleColumn(typeName as string, key as string) as bool:
      #typeName = getAssociatedTypeNameFromTable(table)
      #debug "isPossibleColumn(${table}, ${key}) _activeRecordTypeColumns.ContainsKey(typeName): ${_activeRecordTypeColumns.ContainsKey(typeName)}"
      if not Utils.IsEmpty(typeName) and _activeRecordTypeColumns.ContainsKey(typeName):
         keys = _activeRecordTypeColumns[typeName]
         idx = Array.IndexOf(keys, key)
         #debug "isPossibleColumn(${typeName}, ${key}) - ${join(keys, ',')} => idx: ${idx}"
         return idx != -1
      else: # if table name was empty or haven't registered this table, then its possible that this is a column
         return true
  
   private static def isRegistered(typeName as string) as bool:
      return not Utils.IsEmpty(typeName) and _activeRecordTypes.ContainsKey(typeName)
   
   
   